﻿CREATE OR REPLACE FUNCTION trgfnwhenupdatepo_updatepol()
  RETURNS trigger AS
	$BODY$ 
		Begin
			IF TG_OP='UPDATE' THEN
				if coalesce(new.tax_per,0)<>coalesce(old.tax_per,0) or coalesce(new.amount_untaxed,0)<>coalesce(old.amount_untaxed,0) or coalesce(new.discount,0)<>coalesce(old.discount,0) or (new.tax_amount=0 and new.tax_amount<>old.tax_amount) then
					Update Purchase_order set 
								amount_total2=coalesce(amount_untaxed,0)-coalesce(discount,0),
								total_amount=coalesce(amount_untaxed,0)-coalesce(discount,0) + (coalesce(amount_untaxed,0)-coalesce(discount,0))*tax_per/100.0,
								tax_amount=(coalesce(amount_untaxed,0)-coalesce(discount,0))*tax_per/100.0
					where id = new.id;
				end if;
				if new.state='done' and old.state<>'done' and new.typeoforder<>(Select id from purchase_typeoforder where code='e') and new.special_case=True then
					Update
						purchase_order_line
					set
						price_subtotal2=price_unit*product_qty - price_unit*product_qty*discount_percent/100.0
					where
						order_id = new.id;

					Update
						purchase_order
					set
						line_update=True,
						amount_untaxed=(Select 
								sum(price_unit*product_qty) as untaxed_amount_before_discount
							    from 
								purchase_order_line where order_id=new.id),
						discount =(Select
								sum(price_unit*product_qty)-sum(price_subtotal2) as discount
							    from 
								purchase_order_line where order_id=new.id),
						discount_percent=(Select 
									case when sum(price_unit*product_qty)=0 
									then 0 
									else (sum(price_unit*product_qty)-sum(price_subtotal2))/sum(price_unit*product_qty)*100.0 end as discount_percent
								from 
									purchase_order_line where order_id=new.id)
					where id=new.id;
				end if;
				
				if new.state<>'done' and old.state='done' and new.typeoforder<>(Select id from purchase_typeoforder where code='e') and new.special_case=True then
					Update
						purchase_order_line
					set
						price_subtotal2=price_unit*plan_qty - price_unit*plan_qty*discount_percent/100.0
					where
						order_id = new.id;

					Update
						purchase_order
					set
						line_update=True,
						amount_untaxed=(Select 
								sum(price_unit*plan_qty) as untaxed_amount_before_discount
							    from 
								purchase_order_line where order_id=new.id),
						discount =(Select
								sum(price_unit*plan_qty)-sum(price_subtotal2) as discount
							    from 
								purchase_order_line where order_id=new.id),
						discount_percent=(Select 
									case when sum(price_unit*plan_qty)=0 
									then 0 
									else (sum(price_unit*plan_qty)-sum(price_subtotal2))/sum(price_unit*plan_qty)*100.0 end as discount_percent
								from 
									purchase_order_line where order_id=new.id)
					where id=new.id;
				end if;				
				
				if new.typeoforder<>(Select id from purchase_typeoforder where code='e') then
					Update
						purchase_order_line 
					set project_id=(Select project_id from purchase_order po where id=new.id) 
					where order_id=new.id and coalesce(project_id,0)=0;
				else
					IF 
						(old.project_id is null and new.project_id>0) or
						(old.project_id<>new.project_id) then
							Update
								purchase_order_line 
							set project_id=(Select project_id from purchase_order po where id=new.id) 
							where order_id=new.id and coalesce(project_id,0)=0;
					End if;
				end if;

				if new.tax_amount>0 and new.tax_amount<>old.tax_amount then
					Update Purchase_order set total_amount=coalesce(amount_untaxed,0)-coalesce(discount,0) + tax_amount
					where id = new.id;
				end if;
				
			End if;
			
			Return NULL;                
		end;
	$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  -- CREATE TRIGGER trgdupdate_insert_po_updatepol
--   AFTER Update or Insert
--   ON purchase_order
--   FOR EACH ROW
--   EXECUTE PROCEDURE trgfnwhenupdatepo_updatepol();